package com.quanjiaolian.chat.serviceimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import org.apache.log4j.Logger;

import com.quanjiaolian.DB.DataBase;
import com.quanjiaolian.bean.ReviewInfo;
import com.quanjiaolian.chat.service.ChatService;

public class ChatServiceImpl implements ChatService {
	
	DataBase db = new DataBase();
	/**
	 * 声明Log4j变量
	 */
	public static ResourceBundle resourceBundle = null;
	static Logger logger = Logger.getLogger(ChatServiceImpl.class.getName());
	
	 /**
    * 声明数据库连接实例
    */
	//定义一个Connection 用来连接数据库 
	Connection conn=null;  
   //声明PreparedStatement实例
   PreparedStatement pstmt = null;
   //用于存放返回结果的数据集
   ResultSet rs = null;
	
	@SuppressWarnings("static-access")
	public List<Object> queryAllChat(int firstSize, int maxSize,int user_type,int st_id) {
		StringBuffer sql = new StringBuffer();
		List<Object> result = new ArrayList<Object>();
		if(user_type == 0){
			sql.append("SELECT (SELECT ui.username FROM user_info ui where ui.userid = ci.user_id) AS user_name, ");
			sql.append("(SELECT ui.userpic_path FROM user_info ui where ui.userid = ci.user_id) AS userpic_path, ");
			sql.append("ci.chat_id, ci.chat_pic, ci.chat_detail, ci.chat_Praise, COUNT(re.chat_id) AS chat_num ");
			sql.append("FROM chat_info ci LEFT JOIN review_info re ON ci.chat_id = re.chat_id WHERE ci.user_type = 0 ");
			if(st_id != 0){
				sql.append("AND st_id = "+st_id+" ");
			}
			sql.append("GROUP BY ci.chat_id ORDER BY ci.send_time DESC ");
			sql.append("LIMIT ?,?");
		}else{
			sql.append("SELECT (SELECT ii.ins_name FROM ins_info ii where ii.ins_id = ci.user_id) AS ins_name, ");
			sql.append("(SELECT ii.inspic_path FROM ins_info ii where ii.ins_id = ci.user_id) AS inspic_path, ");
			sql.append("ci.chat_id, ci.chat_detail, ci.chat_pic, ci.chat_Praise, COUNT(re.chat_id) AS chat_num ");
			sql.append("FROM chat_info ci LEFT JOIN review_info re ON ci.chat_id = re.chat_id WHERE ci.user_type = 1 ");
			if(st_id != 0){
				sql.append("AND st_id = "+st_id+" ");
			}
			sql.append("GROUP BY ci.chat_id ORDER BY ci.send_time DESC ");
			sql.append("LIMIT ?,?");
		}
		System.out.println("--------------------------queryAllChat sql is : "+sql.toString());
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1,(firstSize-1)*maxSize);
			pstmt.setInt(2,maxSize);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("user_name", rs.getString(1));
				map.put("user_pic", rs.getString(2));
				map.put("chat_id", rs.getInt(3));
				map.put("chat_pic", rs.getString(4));
				map.put("chat_detail", rs.getString(5));
				map.put("chat_praise", rs.getInt(6));
				map.put("chat_num", rs.getInt(7));
				result.add(map);
			}
		} catch (SQLException e) {
			logger.info("queryAllChat error!");
			e.printStackTrace();
			return null;
		}finally{
			db.close(conn, pstmt, rs);
		}
		
		return result;
	}
	
	@SuppressWarnings("static-access")
	public Map<String,Object> updatePraiseByChatId(int chat_id){
		Map<String,Object> result = new HashMap<String,Object>();
		int i = 0;
		String sql = "";
		conn = db.getconn();
		try {
			sql = "select chat_praise from chat_info where chat_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,chat_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				i = rs.getInt(1);
			}else{
				result.put("result","no chat_id");
				return result;
			}
			sql = "update chat_info set chat_praise = ? where chat_id = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,i+1);
			pstmt.setInt(2,chat_id);
			i = pstmt.executeUpdate();
			if(i>0){
				result.put("result","OK");
			}else{
				result.put("result","Failed");
			}
			
		} catch (SQLException e) {
			logger.info("updatePraiseByChatId error!");
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return result;
	}
	/**
	 * 查询热门运动列表
	 */
	@SuppressWarnings("static-access")
	public List<Object> queryHotSport(){
		List<Object> result = new ArrayList<Object>();
		
		String sql = "";
		conn = db.getconn();
		try {
			sql = "select '热门训练' AS parents_name,st.st_id,st.st_name,st.st_img from sport_type st where st.st_ishot = 0";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String, Object>();
				map.put("parents_name", rs.getString(1));
				map.put("st_id", rs.getInt(2));
				map.put("st_name", rs.getString(3));
				map.put("st_img", rs.getString(4));
				result.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return result;
	}
	
	/**
	 * 查询所有父类ID
	 * @return
	 */
	@SuppressWarnings("static-access")
	public List<Map<String,Object>> queryAllType(){
		List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
		String sql = "";
		conn = db.getconn();
		try {
			sql = "select st.st_id,st.st_name from sport_type st where st.st_parents_id = 0";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String, Object>();
				map.put("st_id",rs.getInt(1));
				map.put("st_name", rs.getString(2));
				result.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return result;
	}
	
	/**
	 * 根据父类ID查询该ID下的所有子项
	 * @param st_id
	 * @return
	 */
	@SuppressWarnings("static-access")
	public List<Object> queryTypeById(String st_id){
		String sql = "SELECT (select st_name FROM sport_type where st_id = ?) AS parents_name,st.st_id,st.st_name,st.st_img from sport_type st WHERE st_parents_id = ?";
		List<Object> result = new ArrayList<Object>();
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,Integer.parseInt(st_id));
			pstmt.setInt(2,Integer.parseInt(st_id));
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String, Object>();
				map.put("parents_name", rs.getString(1));
				map.put("st_id", rs.getInt(2));
				map.put("st_name", rs.getString(3));
				map.put("st_img", rs.getString(4));
				result.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return result;
	}
	
	@SuppressWarnings("static-access")
	public int insertSendMsg(int user_id,String chat_detail,String chat_pic,String user_agent,int st_id,int user_type){
		int i = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("insert into chat_info ");
		sql.append("(user_id,chat_detail,chat_praise,send_time,user_agent,st_id,user_type,chat_pic) ");
		sql.append("values ");
		sql.append("(?,?,?,?,?,?,?,?)");
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1,user_id);
			pstmt.setString(2,chat_detail);
			pstmt.setInt(3,0);
			pstmt.setTimestamp(4, new java.sql.Timestamp(System.currentTimeMillis()));
			pstmt.setString(5,user_agent);
			pstmt.setInt(6,st_id);
			pstmt.setInt(7,user_type);
			pstmt.setString(8,chat_pic);
			i = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return i;
	}
	
	@SuppressWarnings("static-access")
	public Map<String,Object> queryChatDetailBychatId(int chat_id,int user_type){
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(user_type == 0){
			sql.append("SELECT ci.chat_id,ui.username,ui.userpic_path,ci.send_time,ci.user_agent,ci.chat_detail,ci.chat_pic,ui.userid ");
			sql.append("FROM chat_info ci,user_info ui ");
			sql.append("WHERE ui.userid = ci.user_id AND ci.chat_id = ?");
		}else{
			sql.append("SELECT ci.chat_id,ii.ins_name,ii.inspic_path,ci.send_time,ci.user_agent,ci.chat_detail,ci.chat_pic,ii.ins_id ");
			sql.append("FROM chat_info ci,ins_info ii ");
			sql.append("WHERE ii.ins_id = ci.user_id AND ci.chat_id = ?");
		}
		System.out.println("queryChatDetailBychatId sql is :"+sql.toString());
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1,chat_id);
			rs = pstmt.executeQuery();
			rs.next();
			map.put("chat_id",rs.getInt(1));
			map.put("user_name",rs.getString(2));
			map.put("userpic_path",rs.getString(3));
			map.put("send_time",sdf.format(rs.getTimestamp(4)));
			map.put("user_agent",rs.getString(5));
			map.put("chat_detail",rs.getString(6));
			map.put("chat_pic",rs.getString(7));
			map.put("user_id", rs.getInt(8));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close(conn, pstmt, rs);
		}
		return map;
	}
	
	@SuppressWarnings("static-access")
	public List<Object> queryAllReviewByChatId(int firstSize,int maxSize,int user_type,int chat_id){
		List<Object> list = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(user_type == 0){
			sql.append("SELECT ri.chat_id,ui.userid,ui.username,ui.userpic_path,ri.review_text,ri.replay_id,ri.replay_name,ri.review_time ");
			sql.append("FROM user_info ui,review_info ri ");
			sql.append("WHERE ui.userid = ri.review_id AND ri.chat_id = ? ");
			sql.append("ORDER BY ri.review_time DESC LIMIT ?,?");
		}else{
			sql.append("SELECT ri.chat_id,ii.ins_id,ii.ins_name,ii.inspic_path,ri.review_text,ri.replay_id,ri.replay_name,ri.review_time ");
			sql.append("FROM ins_info ii,review_info ri ");
			sql.append("WHERE ii.ins_id = ri.review_id AND ri.chat_id = ? ");
			sql.append("ORDER BY ri.review_time DESC LIMIT ?,?");
		}
		System.out.println(sql.toString());
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1,chat_id);
			pstmt.setInt(2,(firstSize-1)*maxSize);
			pstmt.setInt(3,maxSize);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Map<String,Object> map = new HashMap<String,Object>();
				map.put("chat_id", rs.getInt(1));
				map.put("user_id", rs.getInt(2));
				map.put("user_name",rs.getString(3));
				map.put("userpic_path",rs.getString(4));
				map.put("review_text", rs.getString(5));
				map.put("replay_id",rs.getInt(6));
				map.put("replay_name",rs.getString(7));
				map.put("review_time",sdf.format(rs.getTimestamp(8)));
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return list;
	}
	
	@SuppressWarnings("static-access")
	public int insertReviewMsg(ReviewInfo review){
		int i = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("insert into review_info (chat_id,review_id,replay_id,replay_name,review_text,review_time,user_type) ");
		sql.append("values (?,?,?,?,?,?,?)");
			
		conn = db.getconn();
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.setInt(1,review.getChat_id());
			pstmt.setInt(2,review.getReview_id());
			pstmt.setInt(3,review.getReplay_id());
			pstmt.setString(4,review.getReplay_name());
			pstmt.setString(5,review.getReview_text());
			pstmt.setTimestamp(6,new java.sql.Timestamp(System.currentTimeMillis()));
			pstmt.setInt(7,review.getUser_type());
			i = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	public static void main(String[] args) {
//		Map<String,Object> map = new HashMap<String, Object>();
//		ChatServiceImpl cs = new ChatServiceImpl();
//		map.put("热门运动", cs.querySportType());
//		List<Map<String,Object>> parentIdList = cs.queryAllType();
//		for(int i=0;i<parentIdList.size();i++){
//			String name = parentIdList.get(i).get("st_name").toString();
//			List<Object> list= cs.queryTypeById(parentIdList.get(i).get("st_id").toString());
//			map.put(name,list);
//		}
//		System.out.println(map);
	}

}
